import os
import uuid
from typing import BinaryIO, List, Optional
from sqlalchemy.orm import Session
from sqlalchemy import desc

from db import models


def create_submission(db: Session, image_data: bytes, filename: str, formula_id: int) -> models.Submission:
    """
    Create a new submission by saving the uploaded image and creating a database record.
    
    Args:
        db: Database session
        image_data: Binary data of the uploaded image
        filename: Original filename of the uploaded image
        formula_id: ID of the formula associated with this submission
        
    Returns:
        Created Submission object
    """
    # Generate unique filename to avoid conflicts
    file_extension = os.path.splitext(filename)[1] if filename else '.jpg'
    unique_filename = f"{uuid.uuid4()}{file_extension}"
    
    # Ensure uploads directory exists
    uploads_dir = "uploads"
    os.makedirs(uploads_dir, exist_ok=True)
    
    # Save image to disk
    image_path = os.path.join(uploads_dir, unique_filename)
    with open(image_path, 'wb') as f:
        f.write(image_data)
    
    # Create database record
    db_submission = models.Submission(
        formula_id=formula_id,
        image_path=image_path,
        status="pending"
    )
    db.add(db_submission)
    db.commit()
    db.refresh(db_submission)
    
    return db_submission


def get_submissions(db: Session, skip: int = 0, limit: int = 100,
                   formula_id: Optional[int] = None, status: Optional[str] = None) -> List[models.Submission]:
    """
    Get a list of submissions with pagination and filtering support.

    Args:
        db: Database session
        skip: Number of records to skip (for pagination)
        limit: Maximum number of records to return
        formula_id: Filter by formula ID (optional)
        status: Filter by status (optional)

    Returns:
        List of Submission objects
    """
    query = db.query(models.Submission)

    # 应用筛选条件
    if formula_id is not None:
        query = query.filter(models.Submission.formula_id == formula_id)

    if status is not None:
        query = query.filter(models.Submission.status == status)

    return query.order_by(desc(models.Submission.created_at)).offset(skip).limit(limit).all()


def get_submission_by_id(db: Session, submission_id: int) -> Optional[models.Submission]:
    """
    Get a single submission by its ID.

    Args:
        db: Database session
        submission_id: ID of the submission to retrieve

    Returns:
        Submission object if found, None otherwise
    """
    return db.query(models.Submission).filter(models.Submission.id == submission_id).first()


def get_submissions_count(db: Session, formula_id: Optional[int] = None,
                         status: Optional[str] = None) -> int:
    """
    Get the total count of submissions with optional filtering.

    Args:
        db: Database session
        formula_id: Filter by formula ID (optional)
        status: Filter by status (optional)

    Returns:
        Total number of submissions matching the filters
    """
    query = db.query(models.Submission)

    # 应用筛选条件
    if formula_id is not None:
        query = query.filter(models.Submission.formula_id == formula_id)

    if status is not None:
        query = query.filter(models.Submission.status == status)

    return query.count()
